package com.daimler.itffo.tables;

import java.util.InputMismatchException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import com.daimler.itffo.uvs.kern.IGenerator;
import com.daimler.itffo.uvs.kern.SQLStatment;
import com.daimler.itffo.uvs.kern.SQLWriter;

public class V0BRB044Type implements IGenerator {
	
	public static int COL_B044_SO_LAND = 1;
	public static int COL_B044_FZ_WHC = 2;
	public static int COL_B044_FZ_ID_NR = 3;
	public static int COL_B044_AF_NR = 4;
	public static int COL_B044_RECHDAT = 5;
	public static int COL_B044_NPREIS = 6;
	public static int COL_B044_MILLESIM = 7;
	public static int COL_B044_BMP = 8;
	public static int COL_B044_GPREIS = 9;
	public static int COL_B044_SAPREIS = 10;
	public static int COL_B044_HERKUNFTS_KZ = 11;
	public static int COL_B044_LIE_VERS_DAT = 12;
	public static int COL_B044_GEBER = 13;
	public static int COL_B044_RECH_ART = 14;
	public static int COL_B044_GVTNR = 17;
	public static int COL_B044_NPREIS_EUR = 18;
	public static int COL_B044_GPREIS_EUR = 19;
	public static int COL_B044_SAPREIS_EUR = 20;

	private String land;
	private String whc;
	private String fin;
	private String Auf_nr;
	private String rechdat;
	private int nPreis;
	private String millesim;
	private int bmp;
	private int gPreis;
	private int saPreis;
	private String herkunft_kz;
	private String lie_ver_dat;
	private String geber;
	private String rech_art;
	private String update_ts;
	private String create_ts;
	private String gvtr;
	private int nPreis_euro;
	private int gPreis_euro;
	private int saPreis_euro;

	private String messages;

	public V0BRB044Type(String land, String whc, String fin, String auf_nr,
			String rechdat, int nPreis, String millesim, int bmp, int gPreis,
			int saPreis, String herkunft_kz, String lie_ver_dat, String geber,
			String rech_art,String gvtr,int nPreis_euro, int gPreis_euro, int saPreis_euro) {
		super();
		this.land = land;
		this.whc = whc;
		this.fin = fin;
		this.Auf_nr = auf_nr;
		this.rechdat = rechdat;
		this.nPreis = nPreis;
		this.millesim = millesim;
		this.bmp = bmp;
		this.gPreis = gPreis;
		this.saPreis = saPreis;
		this.herkunft_kz = herkunft_kz;
		this.lie_ver_dat = lie_ver_dat;
		this.geber = geber;
		this.rech_art = rech_art;
		this.update_ts = DEFAULT_TS;
		this.create_ts = DEFAULT_TS;
		this.gvtr = gvtr;
		this.nPreis_euro = nPreis_euro;
		this.gPreis_euro = gPreis_euro;
		this.saPreis_euro = saPreis_euro;
	}

	public V0BRB044Type() {

	}

	@Override
	public String generate(Sheet sheet, String folder, String str,
			String uStr) throws Exception{
		SQLWriter fileWriterV0BRB044EU;
		fileWriterV0BRB044EU = new SQLWriter();
		fileWriterV0BRB044EU.openFile(folder + '\\'
				+ SQLWriter.FILENAME_B044_EU);
		String colChanged;
		String colFzIdNr;
		String colLand;
		String colWhc;
		String colAuf_nr;
		String colRechdat;
		int colNpreis;
		String colMillesim;
		int colBmp;
		int colGpreis;
		int colSaPreis;
		String colHerkunft_kz;
		String colLie_ver_dat;
		String colGeber;
		String colRech_art;
		String colGvtr;
		int colNpreis_euro;
		int colGpreis_euro;
		int colsSaPreis_euro;
		V0BRB044Type b044;
		Row row;

		SQLStatment sqlid;
		sqlid = new SQLStatment();
		sqlid.addFragment("SET CURRENT SQLID='" + str + "';");
		sqlid.addFragment(SQLWriter.NEWLINE);
		fileWriterV0BRB044EU.write(sqlid);
		int active_row = 1;
		int action;
		int rows_ignored = 0;
		int row_inserted = 0;
		int row_deleted =0;
		int row_count = 0;
		while (true) {
			row = sheet.getRow(active_row);
			if (isRowTheLastRow(row)) {
				fileWriterV0BRB044EU.write(commitStatment());

				break;
			}

			colChanged = getValueOfStringCell(row.getCell(COL_TABLE_CHANGED));
			action = checkAction(colChanged);
			if (action == ACTION_ERROR) {
				messages = "Fehler: Die (Steuer-)Zelle "
						+ getStringPos(row.getCell(COL_TABLE_CHANGED))
						+ " der folgende Wert enthalten:\n";
				messages += "Update: 'U'\n";
				messages += "Keine Aktion: ''\n";
				return messages;
			}

			if (action != ACTION_NONE) {
				colLand = getValueOfStringCell(row.getCell(COL_B044_SO_LAND));
				colWhc = getValueOfStringCell(row.getCell(COL_B044_FZ_WHC));
				colFzIdNr = getValueOfStringCell(row.getCell(COL_B044_FZ_ID_NR));
				colAuf_nr = getValueOfStringCell(row.getCell(COL_B044_AF_NR));
				colRechdat = getValueOfStringCell(row.getCell(COL_B044_RECHDAT));				
				colNpreis = getValueOfIntCell(row.getCell(COL_B044_NPREIS));
				colMillesim = getValueOfStringCell(row.getCell(COL_B044_MILLESIM));
				colBmp = getValueOfIntCell(row.getCell(COL_B044_BMP));
				colGpreis = getValueOfIntCell(row.getCell(COL_B044_GPREIS));
				colSaPreis = getValueOfIntCell(row.getCell(COL_B044_SAPREIS));
				colHerkunft_kz = getValueOfStringCell(row.getCell(COL_B044_HERKUNFTS_KZ));
				colLie_ver_dat = getValueOfStringCell(row.getCell(COL_B044_LIE_VERS_DAT));
				colGeber = getValueOfStringCell(row.getCell(COL_B044_GEBER));
				colRech_art = getValueOfStringCell(row.getCell(COL_B044_RECH_ART));
				colGvtr = getValueOfStringCell(row.getCell(COL_B044_GVTNR));
				colNpreis_euro = getValueOfIntCell(row.getCell(COL_B044_NPREIS_EUR));
				colGpreis_euro = getValueOfIntCell(row.getCell(COL_B044_GPREIS_EUR));
				colsSaPreis_euro = getValueOfIntCell(row.getCell(COL_B044_SAPREIS_EUR));

				b044 = new V0BRB044Type(colLand, colWhc, colFzIdNr, colAuf_nr,
						colRechdat, colNpreis, colMillesim, colBmp, colGpreis,
						colSaPreis, colHerkunft_kz, colLie_ver_dat, colGeber,
						colRech_art, colGvtr,colNpreis_euro, colGpreis_euro, colsSaPreis_euro);

				fileWriterV0BRB044EU.write(b044.createSQLDescription());
				fileWriterV0BRB044EU.write(b044.createSQLDeleteStatment());

				if (action == ACTION_INSERT_AND_DELETE)
					fileWriterV0BRB044EU.write(b044.createSQLStatment(false));
				
				row_count++;

				if (row_count % 250 == 0) {
					fileWriterV0BRB044EU.write(b044.commitStatment());
				}

			}

			if (action == ACTION_NONE)
				rows_ignored++;

			if (action == ACTION_DELETE)
				row_deleted++;

			if (action == ACTION_INSERT_AND_DELETE)
				row_inserted++;

			active_row++;
		}
		messages = "Zeilen verarbeitet: " + (active_row - 1) + "\n";
		messages += "Zeilen ignoriert: " + rows_ignored + "\n";
		messages += "Zeilen geloescht: " + row_deleted + "\n";
		messages += "Zeilen eingefuegt: " + row_inserted + "\n";

		fileWriterV0BRB044EU.close();

		return messages;

	}

	@Override
	public int checkAction(String action) {
		if (action == null)
			return ACTION_ERROR;
		String upperAction = action.toUpperCase();
		if (ACTION_NONE_STRING.equals(upperAction))
			return ACTION_NONE;

		if (ACTION_DELETE_STRING.equals(upperAction))
			return ACTION_DELETE;

		if (ACTION_INSERT_AND_DELETE_STRING.equals(upperAction))
			return ACTION_INSERT_AND_DELETE;

		return ACTION_ERROR;
	}

	@Override
	public SQLStatment commitStatment() {
		SQLStatment commit = new SQLStatment();
		commit.addFragment("COMMIT;" + SQLWriter.NEWLINE);
		return commit;
	}

	@Override
	public SQLStatment createSQLDeleteStatment() {
		SQLStatment sql = new SQLStatment();

		sql.addFragment("DELETE FROM V0BRB044 ");
		sql.addFragment("WHERE ");
		sql.addFragment("B044_SO_LAND = '"
				+ this.land + "' ");
		sql.addFragment("AND B044_FZ_WHC = '" + this.whc + "' ");
		sql.addFragment("AND B044_FZ_ID_NR = '"
				+ this.fin + "' ;");
		sql.addFragment(SQLWriter.NEWLINE);
		return sql;
	}

	@Override
	public SQLStatment createSQLDescription() {
		SQLStatment sql = new SQLStatment();
		sql.addFragment("--" + this.fin);
		sql.addFragment("------------------------------------------------------------------------");
		return sql;
	}

	@Override
	public void checkCellStyle(Cell cell, int type)
			throws InputMismatchException {
		if (cell.getCellType() == type)
			return;

		throw new InputMismatchException("Zelle " + getStringPos(cell)
				+ " muss als " + getCellStyleStringRep(type)
				+ " definiert sein.\n" + "Zelle " + getStringPos(cell)
				+ " ist als " + getCellStyleStringRep(cell.getCellType()) + "("
				+ cell.getCellType() + ") definiert"

		);

	}

	@Override
	public String colToString(int col) {
		switch (col) {
		case 1:
			return "A";
		case 2:
			return "B";
		case 3:
			return "C";
		case 4:
			return "D";
		case 5:
			return "E";
		case 6:
			return "F";
		case 7:
			return "G";
		case 8:
			return "H";
		case 9:
			return "I";
		case 10:
			return "J";
		case 11:
			return "K";
		case 12:
			return "L";
		case 13:
			return "M";
		case 14:
			return "N";
		case 15:
			return "O";
		case 16:
			return "P";

		default:
			break;
		}
		return "Ausserhalb des Verabreitungsbereich";
	}

	@Override
	public String getCellStyleStringRep(int type) {
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			return "TEXT";
		case Cell.CELL_TYPE_NUMERIC:
			return "ZAHL";
		case Cell.CELL_TYPE_FORMULA:
			return "FORMEL";
		case Cell.CELL_TYPE_BLANK:
			return "LEER";
		default:
			break;
		}
		return "UNBEKANNT";
	}

	@Override
	public String getStringPos(Cell cell) {
		return colToString(cell.getColumnIndex() + 1) + "-"
				+ (cell.getRowIndex() + 1);
	}

	@Override
	public int getValueOfIntCell(Cell cell) throws InputMismatchException {
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			String value = getValueOfStringCell(cell);
			if (value.equals("")) {
				throw new InputMismatchException("Zelle " + getStringPos(cell)
						+ " muss einen (Numerischen) Wert enthalten");
			}
			return Integer.valueOf(value).intValue();
		}

		checkCellStyle(cell, Cell.CELL_TYPE_NUMERIC);

		Double d = cell.getNumericCellValue();
		return d.intValue();
	}

	@Override
	public String getValueOfStringCell(Cell cell)
			throws InputMismatchException {
		String value = new String("");

		if (cell == null)
			return value;

		if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
			return new String("");

		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();

		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			int i = (int) cell.getNumericCellValue();
			value = String.valueOf(i);
		} else {
			throw new InputMismatchException("Zelle " + getStringPos(cell)
					+ " ist als " + getCellStyleStringRep(cell.getCellType())
					+ " definiert. Dies kann nicht verarbeit werden.");
		}

		if (value == null)
			return new String("");

		return value.trim();
	}

	@Override
	public boolean isRowTheLastRow(Row row) {
		if (row == null)
			return true;

		Cell bmCell = row.getCell(COL_B044_FZ_ID_NR);

		if (bmCell == null)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_BLANK)
			return true;

		if (bmCell.getCellType() == Cell.CELL_TYPE_STRING) {
			String s = getValueOfStringCell(row.getCell(1));
			if (s == null)
				return true;
			if (!s.equals(""))
				return false;
		}
		return true;
	}

	@Override
	public SQLStatment createSQLStatment(boolean b) {
		System.out.println("createSqlstatment");
		SQLStatment sql = new SQLStatment();
		sql.addFragment("INSERT INTO V0BRB044 ");
		sql.addFragment("(B044_SO_LAND, ");
		sql.addFragment("B044_FZ_WHC, ");
		sql.addFragment("B044_FZ_ID_NR, ");
		sql.addFragment("B044_AF_NR, ");
		sql.addFragment("B044_RECHDAT, ");
		sql.addFragment("B044_NPREIS, ");
		sql.addFragment("B044_MILLESIM, ");
		sql.addFragment("B044_BMP, ");
		sql.addFragment("B044_GPREIS, ");
		sql.addFragment("B044_SAPREIS, ");
		sql.addFragment("B044_HERKUNFTS_KZ, ");
		sql.addFragment("B044_LIE_VERS_DAT, ");
		sql.addFragment("B044_GEBER, ");
		sql.addFragment("B044_RECH_ART, ");
		sql.addFragment("B044_UPDATE_TS, ");
		sql.addFragment("B044_CREATE_TS, ");
		sql.addFragment("B044_GVTNR, ");
		sql.addFragment("B044_NPREIS_EUR, ");
		sql.addFragment("B044_GPREIS_EUR, ");
		sql.addFragment("B044_SAPREIS_EUR) ");
		sql.addFragment("VALUES (");
		sql.addValue(this.land, false);
		sql.addValue(this.whc, false);
		sql.addValue(this.fin, false);
		sql.addValue(this.Auf_nr, false);
		sql.addValue(this.rechdat, false);
		sql.addValue(this.nPreis, false);
		sql.addValue(this.millesim, false);
		sql.addValue(this.bmp, false);
		sql.addValue(this.gPreis, false);
		sql.addValue(this.saPreis, false);
		sql.addValue(this.herkunft_kz, false);
		sql.addValue(this.lie_ver_dat, false);		
		sql.addValue(this.geber, false);
		sql.addValue(this.rech_art, false);
		sql.addNotQuetedValue(this.update_ts, false);
		sql.addNotQuetedValue(this.create_ts, false);
		sql.addValue(this.gvtr, false);
		sql.addValue(this.nPreis_euro, false);
		sql.addValue(this.gPreis_euro, false);
		sql.addValue(this.saPreis_euro, true);
		sql.addFragment(SQLWriter.NEWLINE);
		return sql;
	}

}
